/**
 * @FileName: ActivityBusinessSalesService.java
 * @Author
 * @Description:
 * @Date 2017年5月18日 下午2:04:12
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ActivityCityBusinessReportService{
    private static final String                     SQL_FILTER_CHANNEL_OPEN         = "oc.WebsiteId=4605 OR oc.WebsiteId=4617 OR oc.WebsiteId=4620 OR oc.WebsiteId=4618 OR oc.WebsiteId=4626 OR oc.WebsiteId=4701 OR oc.WebsiteId=4612";
    private static final String                     SQL_FILTER_CHANNEL_VILLAGE      = "oc.WebsiteId = 4603";
    // 渠道类型：统计月报表使用
    public static final int                         CHANNEL_TYPE_VILLAGE            = 0;                                                                                                                                                // 村渠店员
    public static final int                         CHANNEL_TYPE_OPEN               = 1;                                                                                                                                                // 开渠店员
    public static final int                         CHANNEL_TYPE_OTHERS             = 2;                                                                                                                                                // 其他渠道店员
    public static final int                         CHANNEL_TYPE_OPEN_FIRM          = 3;                                                                                                                                                // 开渠厂促店员
    public static final int                         CHANNEL_TYPE_ALL                = 4;                                                                                                                                                // 所有店员
    // 统计类型
    public static final int                         COUNT_TYPE_DAILY_LIVENESS       = 0;                                                                                                                                                // 当日活跃用户数
    public static final int                         COUNT_TYPE_DAILY_20_YUAN        = 1;                                                                                                                                                // 当日成功领取20元用户数
    public static final int                         COUNT_TYPE_DAILY_PRODUCT        = 2;                                                                                                                                                // 当日4G+全网手机发展量
    public static final int                         COUNT_TYPE_DAILY_PRODUCT_30YUAN = 3;                                                                                                                                                // 当日成功领取终端销售奖励用户数

    public static ActivityCityBusinessReportService service                         = new ActivityCityBusinessReportService();

    // 获取村渠活跃度
    public List<Record> getCityReport(String startDate, String endDate){
        String sql = "SELECT id, DisplayName, cnt20, cnt30 FROM organization org"
                + " LEFT JOIN ( SELECT COUNT(DISTINCT asp.phone_no) AS cnt20, CityCode"
                + " FROM activity_sales_promotion_returned_fare asp, member_org mmo, organization org"
                + " WHERE asp.phone_no = mmo.user_name AND mmo.org_id = org.id AND amount = 20 AND opt_time>=? AND opt_time<=? GROUP BY org.CityCode )"
                + " tmp ON org.id = tmp.CityCode LEFT JOIN ( SELECT COUNT(DISTINCT asp.phone_no) AS cnt30,"
                + " CityCode FROM activity_sales_promotion_returned_fare asp, member_org mmo, organization"
                + " org WHERE asp.phone_no = mmo.user_name AND mmo.org_id = org.id AND amount = 30 AND opt_time>=? AND opt_time<=? GROUP BY org.CityCode )"
                + " tmp1 ON org.id = tmp1.CityCode WHERE org.id = org.CityCode ORDER BY id ASC ";
        return Db.find(sql, getStartTime(startDate), getEndTime(endDate), getStartTime(startDate), getEndTime(endDate));
    }

    public List<Record> getAreaReport(String cityCode, String startDate, String endDate){
        String sql = "SELECT id, DisplayName, cnt20, cnt30 FROM organization org"
                + " LEFT JOIN ( SELECT COUNT(DISTINCT asp.phone_no) AS cnt20, AreaCode"
                + " FROM activity_sales_promotion_returned_fare asp, member_org mmo, organization org"
                + " WHERE asp.phone_no = mmo.user_name AND mmo.org_id = org.id AND amount = 20 AND"
                + " opt_time >= ? AND opt_time <= ? GROUP BY org.AreaCode )"
                + " tmp ON org.id = tmp.AreaCode LEFT JOIN ( SELECT COUNT(DISTINCT asp.phone_no) AS cnt30,"
                + " AreaCode FROM activity_sales_promotion_returned_fare asp, member_org mmo, organization org"
                + " WHERE asp.phone_no = mmo.user_name AND mmo.org_id = org.id AND amount = 30 AND"
                + " opt_time >= ? AND opt_time <= ? GROUP BY org.AreaCode ) tmp1 ON org.id = tmp1.AreaCode"
                + " WHERE org.id = org.AreaCode AND org.CityCode = ? ORDER BY id ASC";
        return Db.find(sql, getStartTime(startDate), getEndTime(endDate), getStartTime(startDate), getEndTime(endDate),
                       cityCode);
    }

    private String getEndTime(String date){
        return date + "23:59:59";
    }

    private String getStartTime(String date){
        return date + " 00:00:00";
    }

}
